/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;
import java.util.Calendar;
import java.util.Vector;
import java.text.*;

/**
 *
 * @author Venus
 */
public class Ban_Model {

    public static int TaoMa() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(maban) from ban";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int TaoTenBan(String makv) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(TenBan) from ban where MaKV = " + makv;
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();

            return 0;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static String LayTinhTrang(int maban, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenTTBan from tinhtrangban, ban where MaNN = " + langid + " and tinhtrangban.MaTTBan = ban.MaTTBan and MaBan = " + maban;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static int LayMaTTBan(int maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int mattban = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select MaTTBan from ban where MaBan = " + maban;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                mattban = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return mattban;
    }

    public static boolean KiemTraBan(String maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select maban from ban where tinhtrang = 1 and (mattban = 1 or mattban = 7 ) and MaBan = " + maban;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            if(!maht.equals(""))
                return true;
            else
                return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }

        }
    }
    public static boolean KiemTraGopBan(String maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select maban from ban where tinhtrang = 1 and mattban = 2 and MaBan = " + maban;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            if(!maht.equals(""))
                return true;
            else
                return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }

        }
    }
    public Vector LayDSBan() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
           ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from ban Order by MaBan desc";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban ban = new Beans.Ban();
                ban.setMaBan(rss.getInt(1));
                ban.setMaKV(rss.getInt(2));
                ban.setMaTTBan(rss.getInt(3));
                ban.setTenBan(rss.getInt(4));
                ban.setSoGhe(rss.getInt(5));
                ban.setTinhTrang(rss.getByte(6));
                v.add(ban);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean LapBan(String makv, String ban, String ghe) {
        Connection conn = null;

        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String x = "insert into ban values ";
            int soban = Integer.parseInt(ban);
            int maban = TaoMa();            
            for (int i = 0; i < soban; i++) {
                x = x + "(" + maban + ", " + makv + ", 1, " + maban + ", " + ghe + " , 1),";
                maban = maban + 1;                
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static String LayTenKV(int makv, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        String tenkv = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenKV from khuvuc where MaKV = " + makv+" and MaNN = "+langid+" ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tenkv = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tenkv;
    }
    
    public static String LayTenBan(int makv, int maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        String tenkv = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenBan from ban where MaKV = " + makv+" and MaBan = "+maban+" ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tenkv = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tenkv;
    }

    public static boolean UpdateStatusBan(String maban, String status) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update ban set TinhTrang = " + status + " where MaBan = " + maban;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static boolean UpdateKVBan(String makv, String maban) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update ban set MaKV = " + makv + " where MaBan = " + maban;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static boolean UpdateStatusBanFollowArea(String makv, String status) {
        Connection conn = null;

        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update ban set TinhTrang = " + status + " where MaKV = " + makv;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean EditSoGhe(String maban, String soghe) {
        Connection conn = null;

        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update ban set soghe = " + soghe + " where MaBan = " + maban;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateTTBan(String mattban, String maban) {
        Connection conn = null;

        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update ban set MaTTBan = " + mattban + " where MaBan = " + maban;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector SearchBan(String tenkv, String tenban, String status, String soghe, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaBan), A.MaKV, MaTTBan, TenBan, SoGhe, A.TinhTrang from ban A, khuvuc B  " +
                           " where A.MaKV = B.MaKV and B.MaNN = "+langid+"  ";
            String[] arr = {tenkv, tenban, soghe, status};
            for (int i = 0; i < arr.length; i++) {
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenkv)){
                    query = query + " and TenKV like '%"+arr[i]+"%' ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)){
                    query = query + " and TenBan = "+arr[i]+" ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soghe)){
                    query = query + " and SoGhe = "+arr[i]+" ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(status) ){
                    query = query + " and A.TinhTrang = "+arr[i]+" ";
                }

            }
            query = query + " Order by A.TinhTrang desc";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenKV(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            String query = "select distinct(MaBan), ban.MaKV, MaTTBan, TenBan, SoGhe, ban.TinhTrang, TenKV from ban, khuvuc where ban.makv = khuvuc.makv Order by TenKV " + order;

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTTKV(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            String query = "select * from ban Order by TinhTrang " + order;

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenKV(int langid, String order, String tenkv, String tenban, String status, String soghe) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            String query = " select distinct(MaBan), A.MaKV, MaTTBan, TenBan, SoGhe, A.TinhTrang from ban A, khuvuc B  " +
                           " where A.MaKV = B.MaKV and B.MaNN = "+langid+"  ";
            String[] arr = {tenkv, tenban, soghe, status};
            for (int i = 0; i < arr.length; i++) {
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenkv)){
                    query = query + " and TenKV like '%"+arr[i]+"%' ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)){
                    query = query + " and TenBan = "+arr[i]+" ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soghe)){
                    query = query + " and SoGhe = "+arr[i]+" ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(status) ){
                    query = query + " and A.TinhTrang = "+arr[i]+" ";
                }
            }
            query = query + " Order by TenKV "+order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTTKV(int langid, String order, String tenkv, String tenban, String status, String soghe) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            String query = " select distinct(MaBan), A.MaKV, MaTTBan, TenBan, SoGhe, A.TinhTrang from ban A, khuvuc B  " +
                           " where A.MaKV = B.MaKV and B.MaNN = "+langid+"  ";
            String[] arr = {tenkv, tenban, soghe, status};
            for (int i = 0; i < arr.length; i++) {
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenkv)){
                    query = query + " and TenKV like '%"+arr[i]+"%' ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)){
                    query = query + " and TenBan = "+arr[i]+" ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soghe)){
                    query = query + " and SoGhe = "+arr[i]+" ";
                }
                if(!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(status) ){
                    query = query + " and A.TinhTrang = "+arr[i]+" ";
                }
            }
            query = query + " Order by A.TinhTrang "+order;

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector Map(String makv) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from ban where MaKV = " + makv + " Order by tenban asc";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public Vector MapDB(String makv) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from ban where MaKV = " + makv + " and TinhTrang = 1 Order by tenban asc";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public Vector MapReserv(String makv, String soghe) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "";
            if(soghe.equals("all")){
                query = "select * from ban where MaKV = " + makv + " and TinhTrang = 1  Order by tenban asc";
            }
            else{
                query = "select * from ban where MaKV = " + makv + " and SoGhe = "+soghe+" and TinhTrang = 1  Order by tenban asc";
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public Vector DetailTable(String maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from ban where maban =" + maban;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public Vector DanhSachTinhTrang(int mattban, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from tinhtrangban where MaTTBan != " + mattban + " and MaNN = " + langid;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TinhTrangBan ttb = new Beans.TinhTrangBan();
                ttb.setMaTTBan(rss.getInt(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTBan(rss.getString(3));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static Vector LayDSTinhTrangBan(String dsma,int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from tinhtrangban where MaNN = " + langid + " and mattban in ("+dsma+")";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TinhTrangBan ttb = new Beans.TinhTrangBan();
                ttb.setMaTTBan(rss.getInt(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTBan(rss.getString(3));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static int LayMaKV(String maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaKV from ban where MaBan = "+maban+" ";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht;
        } catch (Exception e) {
            e.printStackTrace();

            return 0;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static Vector LoadDSBan(String makv,String tinhtrang)
     {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "";
            if(tinhtrang.equals("free"))
            {
                query = "select * from ban where makv = "+makv+" and tinhtrang = 1 and (MaTTBan = 1 or MaTTBan = 7) order by maban";
            }
            else if (tinhtrang.equals("update"))
            {
                query = "select * from ban where makv = "+makv+" and tinhtrang = 1 and (MaTTBan = 2) order by maban";
            }
            else if (tinhtrang.equals("change"))
            {
                query = "select * from ban where makv = "+makv+" and tinhtrang = 1 and (MaTTBan = 1 or MaTTBan = 4 or MaTTBan = 5 or MaTTBan = 6) order by maban";
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Ban b = new Beans.Ban();
                b.setMaBan(rss.getInt(1));
                b.setMaKV(rss.getInt(2));
                b.setMaTTBan(rss.getInt(3));
                b.setTenBan(rss.getInt(4));
                b.setSoGhe(rss.getInt(5));
                b.setTinhTrang(rss.getByte(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        }
        catch (Exception e) {
            e.getMessage();

        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
     }

    public static boolean UpdateTTBanMoi(String mattban, String maban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String query = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            query = "Update ban set MaTTBan = " + mattban + " where MaTTBan not in (2,3,7,8) and MaBan = " + maban;

            if(mattban.equals("8"))
                query = "Update ban set MaTTBan = " + mattban + " where MaBan = " + maban;
            int n = stmt.executeUpdate(query);
            if(mattban.equals("8"))
            {
                int sohdt = 0;
                query = "select sohdt from phieudatmonan where MaBan = " + maban;
                rss = stmt.executeQuery(query);
                while (rss.next()) {
                    sohdt = rss.getInt(1);
                }
                query = "Delete from chitietpdma where ( MaTTMA = 1 or MaTTMA = 4 ) and SoHDT = " + sohdt;
                n = stmt.executeUpdate(query);
                 query = "select sum(thanhtien) from chitietpdma where sohdt = " + sohdt;
                rss = stmt.executeQuery(query);
                double thanhtien = 0d;
                while (rss.next()) {
                    thanhtien = rss.getDouble(1);
                }
                query = "update phieudatmonan set thanhtien = " + thanhtien + ",tongcong=thanhtien+(phiphucvu*thanhtien/100)-(chietkhau*thanhtien/100) where sohdt=" + sohdt;
                n = stmt.executeUpdate(query);
            }
            if(mattban.equals("1"))
            {
                String query1 = "select maban from datban where maban = " + maban;
                rss = stmt.executeQuery(query1);
                int ban = 0;
                while (rss.next()) {
                    ban = rss.getInt(1);
                }
                if(ban != 0)
                {
                     query = "Update ban set MaTTBan = 7  where MaBan = " + maban;
                     n = stmt.executeUpdate(query);
                }
            }
            stmt.close();
            if(rss != null)
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
             if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static int GopBan(String tuban,String denban) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select SoHDT,SoKhach from phieudatmonan where MaBan = "+tuban;
            rss = stmt.executeQuery(query);
            int sohd1 = 0;
            int sokhach1 = 0;
            while (rss.next()) {
                sohd1 = rss.getInt(1);
                sokhach1 = rss.getInt(2);
            }
             query = "select SoHDT,SoKhach from phieudatmonan where MaBan = "+denban;
            rss = stmt.executeQuery(query);
            int sohd2 = 0;
            int sokhach2 = 0;
            int sokhach = sokhach1 + sokhach2;
            while (rss.next()) {
                sohd2 = rss.getInt(1);
                sokhach2 = rss.getInt(2);
            }
            if(sohd1 == 0 || sohd2 ==0)
            {
                return 0;
            }
            
            query = "update chitietpdma set sohdt = "+sohd2+" where SoHDT = "+sohd1;
            stmt.executeUpdate(query);
            query = "delete from phieudatmonan where SoHDT = "+sohd1;
            stmt.executeUpdate(query);
            query = "update ban set mattban = 1 where maban = "+tuban;
            stmt.executeUpdate(query);
            query = "select sum(thanhtien) from chitietpdma where sohdt = " + sohd2;
            rss = stmt.executeQuery(query);
            double thanhtien = 0d;
            while (rss.next()) {
                thanhtien = rss.getDouble(1);
            }
            query = "update phieudatmonan set sokhach = "+sokhach+",thanhtien = " + thanhtien + ",tongcong=thanhtien+(phiphucvu*thanhtien/100)-(chietkhau*thanhtien/100) where sohdt=" + sohd2;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            rss.close();
            if (n == 0) {
                return 1;// thất bại
            }
            return 2;
        } catch (Exception e) {
            e.printStackTrace();

            return 1;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static int ChuyenBan(String tuban,String denban,String kv) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            Calendar now = Calendar.getInstance();
            int h = now.get(Calendar.HOUR_OF_DAY);
            int p = now.get(Calendar.MINUTE);
            String ngay = String.valueOf(now.get(Calendar.DAY_OF_WEEK));
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
            String ngaygio = fm.format(now.getTime());
            String matd = ThoiDiem_Model.TimMaTD(h, p, 1);
            int phiphucvu = PhiPV_Model.LoadPPV(kv,matd);
            String query = "update phieudatmonan set maban = "+denban+",phiphucvu = " + phiphucvu + " where maban=" + tuban;
            int n = stmt.executeUpdate(query);
            query = "update ban set mattban = 1 where maban = "+tuban;
            stmt.executeUpdate(query);
            query = "update ban set mattban = 2 where maban = "+denban;
            stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return 1;// thất bại
            }
            return 2;
        }
        catch (Exception e) {
            e.printStackTrace();

            return 1;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}
